<?php
/**
 * 検索 再検索左パネルクラス<br />
 *
 * @package apamanshop
 * @author imamura-h@systemsoft.co.jp
 * @copyright コピーライト(要確認)
 */
class Search_Model_ResearchPanel extends Search_Model_Base
{
	// DBコネクション
	private $_db;

	/**
	 * コンストラクタ
	 */
	function __construct() {
		$this->_db = $this->getDbConnection();
		//parent::__construct();
	}
	/**
	 * デストラクタ
	 */
	function __destruct() {
		if (!empty($this->_db)) {
			$this->_db->closeConnection();
		}
		//parent::__destruct();
	}

	/**
	*  再検索左パネルデータを取得
	*/
	public function convertResarchPanel($searchKbn, $params)
	{
		// COMMON
		$this->_model_common = new Search_Model_Common();

		// パラメータ取得
		$this->_data = array();
		$this->_data['search_kbn'] = $searchKbn;

		// 路線図
		$sql = "";
		$db_params = array();
		if ($this->_model_common->checkvalue($params, 'stcode'))
		{
			// 路線図駅CDが存在している場合
			$sql = $this->generateStcodeSql($params, $db_params);
			// 一覧取得
			$result = $this->dbGetList($sql, $db_params, $this->_db);
			$this->convertRosenzuData($result, $this->_data);
		}
		// 路線図：駅名入力
		if ($this->_model_common->checkvalue($params, 'ekiname'))
		{
			$this->_data['ekiname'] = $params['ekiname'];
		}
		// 路線図：駅名選択
		if ($this->_model_common->checkvalue($params, 'RsCd'))
		{
			$this->_data['RsCd'] = $params['RsCd'];
		}

		// 学校検索：CD
		$sql = "";
		$db_params = array();
		if ($this->_model_common->checkvalue($params, 'sch_cd'))
		{
			// 学校CDが存在している場合
			if ($params['SrhType'] == Apm_Config_Search::SRHTYPE_LIST) {
				// 一覧から検索の場合
				$sql = $this->generateSchCdListSql($params, $db_params);
			} else if ($params['SrhType'] == Apm_Config_Search::SRHTYPE_MAP) {
				// 地図から検索の場合
				$sql = $this->generateSchCdMapSql($params, $db_params);
			}
			// データ取得
			if (!empty($sql)) {
				$result = $this->dbGetData($sql, $db_params);
				//if (!isset($data['sch_cd'])) $data['sch_cd'] = array();
				if (!empty($result)) {
					$this->_data['sch_cd'] = $result['SCH_NAME'];
				}
			}
		}
		// 学校検索：入力
		if ($this->_model_common->checkvalue($params, 'sch_name'))
		{
			$this->_data['sch_name'] = $params['sch_name'];
		}

		//  駅名検索
		if ($this->_model_common->checkvalue($params, 'StWord'))
		{
			$this->_data['StWord'] = $params['StWord'];
		}

		//  通勤・通学時間検索
		if ($this->_model_common->checkvalue($params, 'TgtStNm'))
		{
			$this->_data['TgtStNm'] = $params['TgtStNm'];
		}

		// 住所検索
		if(   $searchKbn == Apm_Config_Search::SRHKBN_JYUSYO
		   || $searchKbn == Apm_Config_Search::SRHKBN_JYUKYOKEITAI
		   || $searchKbn == Apm_Config_Search::SRHKBN_KODAWARI
		   || $searchKbn == Apm_Config_Search::SRHKBN_TOKUSYU
		   || $searchKbn == Apm_Config_Search::SRHKBN_CAMPAIGN
		   || $searchKbn == Apm_Config_Search::SRHKBN_CHIZU
		   || $searchKbn == Apm_Config_Search::SRHKBN_GAKKONAME){

			$sql = "";
			$db_params = array();

			// 都道府県
			if ($this->_model_common->checkvalue($params, 'PrefCd'))
			{
				$sql = "";
				$db_params = array();
				$sql = $this->generatePrefSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}

			// 町CDが存在している場合
			if ($this->_model_common->checkvalue($params, 'TownCd'))
			{
				$sql = "";
				$db_params = array();
				$sql = $this->generateCyoNameByCyoCdSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}

			// 市区町村CDが存在している場合
			if ($this->_model_common->checkvalue($params, 'CityCd'))
			{
				$sql = "";
				$db_params = array();
				$sql = $this->generateShikucyosonNameByShikucyosonCdSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}
/*
			// 市区町村CDが存在している場合（地図検索の場合）
			if (   $this->_model_common->checkvalue($params, 'CityCd')
			    && $searchKbn == Apm_Config_Search::SRHKBN_CHIZU)
			{
				$sql = "";
				$db_params = array();
				$sql = $this->generateChizuCitySql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}
*/
			// 政令指定都市
			if ($this->_model_common->checkvalue($params, 'SiteitosiCd'))
			{
				$sql = "";
				$db_params = array();
				$sql = $this->generateSiteiSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}

		}
		// 沿線・駅検索
		elseif(   $searchKbn == Apm_Config_Search::SRHKBN_ENSENEKI
		       || $searchKbn == Apm_Config_Search::SRHKBN_ROSENZU
			   || $searchKbn == Apm_Config_Search::SRHKBN_SUMITAIEKI
		       || $searchKbn == Apm_Config_Search::SRHKBN_TUKINTUGAKU){

			// 沿線・駅
			if (empty($this->_data['PrefCd']) && $this->_model_common->checkvalue($params, 'PrefCd'))
			{
				$sql = "";
				$db_params = array();
				$sql = $this->generatePrefSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertEnsenekiData($result, $this->_data);
			}

			// 駅
       		if ($this->_model_common->checkvalue($params, 'StCd'))
			{

				// 駅CDが存在している場合
				$sql = $this->generateStNameByStCdSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertEnsenekiData($result, $this->_data);
			}

			// 沿線
			if ($this->_model_common->checkvalue($params, 'RailCd'))
			{
				// 沿線CDが存在している場合
				$sql = "";
				$db_params = array();
				$sql = $this->generateRailNameByRailCdSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertEnsenekiData($result, $this->_data);
			}

		}
		// マイページ検索条件登録検索
		// マイページ検索条件変更検索
		// 物件リクエスト検索
		// 社宅検索
		// 社宅物件リクエスト検索
		elseif(   $searchKbn == Apm_Config_Search::SRHKBN_MYPAGE_ADD
		       || $searchKbn == Apm_Config_Search::SRHKBN_MYPAGE_UPD
		       || $searchKbn == Apm_Config_Search::SRHKBN_BUKENREQUEST
		       || $searchKbn == Apm_Config_Search::SRHKBN_SYATAKU
		       || $searchKbn == Apm_Config_Search::SRHKBN_BUKENREQUEST_SYATAKU
		       || $searchKbn == Apm_Config_Search::SRHKBN_YACHINSOUBA
		       || $searchKbn == Apm_Config_Search::SRHKBN_CHINRYOMADORI
		       || $searchKbn == Apm_Config_Search::SRHKBN_AREA
		       ){

		    /*
		     * 住所
		     */
		    // 都道府県
			if (empty($this->_data['PrefCd']) && $this->_model_common->checkvalue($params, 'PrefCd'))
			{
				$db_params = array();
				$sql = $this->generatePrefSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}

		    // 町CDが存在している場合
			if ($this->_model_common->checkvalue($params, 'TownCd'))
			{
				$db_params = array();
				$sql = $this->generateCyoNameByCyoCdSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}

			// 市区町村CDが存在している場合
			if (   $this->_model_common->checkvalue($params, 'CityCd')
			    && $searchKbn != Apm_Config_Search::SRHKBN_CHIZU)
			{
				$db_params = array();
				$sql = $this->generateShikucyosonNameByShikucyosonCdSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}

			// 市区町村CDが存在している場合（地図検索の場合）
			if (   $this->_model_common->checkvalue($params, 'CityCd')
			    && $searchKbn == Apm_Config_Search::SRHKBN_CHIZU)
			{
				$db_params = array();
				$sql = $this->generateChizuCitySql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}

			// 政令指定都市
			if ($this->_model_common->checkvalue($params, 'SiteitosiCd'))
			{
				// 政令指定都市CDが存在している場合
				$db_params = array();
				$sql = $this->generateSiteiSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertJyusyoData($result, $this->_data);
			}

			/*
			 * 沿線・駅
			 */
/*			// 都道府県
			if (empty($this->_data['PrefCd']) && $this->_model_common->checkvalue($params, 'PrefCd'))
			{
				$db_params = array();
				$sql = $this->generatePrefSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertEnsenekiData($result, $this->_data);
			}
*/
		    // 駅
       		if ($this->_model_common->checkvalue($params, 'StCd'))
			{
				// 駅CDが存在している場合
				$db_params = array();
				$sql = $this->generateStNameByStCdSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertEnsenekiData($result, $this->_data);
			}

			// 沿線
			if ($this->_model_common->checkvalue($params, 'RailCd'))
			{
				// 沿線CDが存在している場合
				$db_params = array();
				$sql = $this->generateRailNameByRailCdSql($params, $db_params);
				$result = $this->dbGetList($sql, $db_params, $this->_db);
				$this->convertEnsenekiData($result, $this->_data);
			}

		}
		// フリーワード検索
		else if($searchKbn == Apm_Config_Search::SRHKBN_FREEWORD)
		{
			// 都道府県選択リスト
			$areaModel = new Search_Model_Selareasearch();
			$this->_data['PrefList'] = $areaModel->getTodohukenList();
		}

		// HP作成契約検索
		else if($searchKbn == Apm_Config_Search::SRHKBN_HP_KEIYAKU)
		{
			// 法人CD取得
			$db_params = array();
			$sql = $this->generateHpkeiyakuHouzinCdSql($params, $db_params);
			$result = $this->dbGetData($sql, $db_params, $this->_db);

			if(!empty($result['T73_HOUZIN_CD'])){
				$this->_data['OemCd'] = $result['T73_HOUZIN_CD'];		// 法人CD
				$this->_data['keiyaku_cd'] = $result['T73_KEIYAKU_CD']; // 契約CD
			}
			else
			{
				// 契約CD
				$this->_data['keiyaku_cd'] = 0;
			}

			if(empty($params['shubetsu']))
			{
				$this->_data['shubetsu'] = 0;
			}
			else
			{
				$this->_data['shubetsu'] = $params['shubetsu'];
			}
		}

		// OEM検索できた場合
		if($this->_model_common->checkvalue($params, 'OemCd')){
			$this->_data['OemCd'] = $params['OemCd'];
			$this->_data['SrhKbn_dtl'] = $params['SrhKbn_dtl'];
		}

		// フリーワード用
		if ($this->_model_common->checkvalue($params, 'freeword')){
			$this->_data['freeword'] = $params['freeword'];
		}

		// 最初にチェックした都道府県コード
		if($this->_model_common->checkvalue($params, "first_check")){
			$this->_data['first_check'] = $params['first_check'];
		}

		// 再検索：都道府県用
		if (isset($params['RePrefCd'])){
			$this->_data['RePrefCd'] = $params['RePrefCd'];
		}

		// 再検索：市区町村用
		if (isset($params['ReCityCd'])){
			$this->_data['ReCityCd'] = $params['ReCityCd'];
		}

		// 再検索：政令指定都市用
		if (isset($params['ReSiteitosiCd'])){
			$this->_data['ReSiteitosiCd'] = $params['ReSiteitosiCd'];
		}

		// 再検索：町用
		if (isset($params['ReTownCd'])){
			$this->_data['ReTownCd'] = $params['ReTownCd'];
		}

		// 再検索：沿線用
		if (isset($params['ReRailCd'])){
			$this->_data['ReRailCd'] = $params['ReRailCd'];
		}

		// 再検索：駅用
		if (isset($params['ReStCd'])){
			$this->_data['ReStCd'] = $params['ReStCd'];
		}

		// 路線図再検索：路線図駅
		if (isset($params['Restcode'])){
			$this->_data['Restcode'] = $params['Restcode'];
		}

		// 学校名再検索：学校名
		if (isset($params['ReSrhName'])){
			$this->_data['ReSrhName'] = $params['ReSrhName'];
		}

		// 学校名再検索：検索区分
		if (isset($params['ReCondition'])){
			$this->_data['ReCondition'] = $params['ReCondition'];
		}

		// 学校名再検索：学校種別
		if (isset($params['ReJanruCd'])){
			$this->_data['ReJanruCd'] = $params['ReJanruCd'];
		}

		// 駅名再検索：駅名
		if (isset($params['ReStWord'])){
			$this->_data['ReStWord'] = $params['ReStWord'];
		}

		// 駅名再検索：検索タイプ
		if (isset($params['ReSrhType'])){
			$this->_data['ReSrhType'] = $params['ReSrhType'];
		}

		// 駅名再検索：検索タイプ
		if (isset($params['SrhType'])){
			$this->_data['SrhType'] = $params['SrhType'];
		}

		// 通勤・通学時間再検索：駅名
		if (isset($params['ReTgtStNm'])){
			$this->_data['ReTgtStNm'] = $params['ReTgtStNm'];
		}

		return $this->_data;
	}

	/**
	 *  町CDからデータ取得用SQL
	 */
	public function generateTownSql($params, &$db_params)
	{

		// WHERE句生成
		$strWhere = "";
		for ($i = 0; $i < count($params['TownCd']); $i++)
		{
			// OR句の取得
			$strOr = ($i != 0) ? " OR" : "";
			// パラメータ
			$db_params['strTownCd_'.$i] = $params['TownCd'][$i];

			$strWhere .= $strOr." ( M01.M01_TODOUFUKEN_CD = substr( :strTownCd_".$i." ,1,2)";
			$strWhere .= " AND M01.M01_SHIKUCHOUSON_CD    = substr( :strTownCd_".$i." ,3,3)";
			$strWhere .= " AND M01.M01_CHOUMEI_CD         = substr( :strTownCd_".$i." ,6,3))";
		}

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M01.M01_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD as SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD || M01.M01_CHOUMEI_CD as CHOUMEI_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS as TODOUFUKEN_ALIAS ";
		$sql .= "    ,to_single_byte(M01.M01_SHIKUCHOUSON) as SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_ALIAS as SHIKUCHOUSON_ALIAS ";
		$sql .= "    ,to_single_byte(M01.M01_CHOUMEI) as CHOUMEI ";
		$sql .= "    ,decode(nvl(M44.M44_SITEITOSI_CD,0),20,to_single_byte(M01.M01_CHOUMEI),M01.M01_SHIKUCHOUSON || to_single_byte(M01.M01_CHOUMEI)) as CHOUMEI_ALIAS ";
		$sql .= "    ,M44.M44_SITEITOSI_CD as SITEITOSI_CD ";
		$sql .= "    ,to_single_byte(M44.M44_SITEITOSI) as SITEITOSI ";
		$sql .= "    ,M44.M44_SITEITOSI_ROMAN as SITEITOSI_ROMAN ";
		$sql .= "    ,M44.M44_SITEITOSI_ALIAS as SITEITOSI_ALIAS ";
		$sql .= "FROM ";
		$sql .= "     M01JYUUSHO M01 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "    ,M44SITEITOSI M44 ";
		$sql .= "    ,M44SITEITOSI_SIKU M44_SIKU ";
		$sql .= "WHERE ";
		$sql .= "        trim(M01_ABOLISH_YM) is null ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = M44_SIKU.M44_TODOUFUKEN_CD(+) ";
		$sql .= "    AND M01.M01_SHIKUCHOUSON_CD = M44_SIKU.M44_SHIKUCHOUSON_CD(+) ";
		$sql .= "    AND M44_SIKU.M44_SITEITOSI_CD = M44.M44_SITEITOSI_CD(+) ";
		$sql .= "    AND (" . $strWhere . ") ";
		$sql .= "GROUP BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_CHOUMEI_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_ALIAS ";
		$sql .= "    ,M01.M01_CHOUMEI ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";
		$sql .= "    ,M01.M01_CHOUMEI_KANA ";
		$sql .= "    ,M44.M44_SITEITOSI_CD ";
		$sql .= "    ,M44.M44_SITEITOSI ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "    ,M44.M44_SITEITOSI_ROMAN ";
		$sql .= "    ,M44.M44_SITEITOSI_ALIAS ";
		$sql .= "ORDER BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";
		$sql .= "    ,M01.M01_CHOUMEI_KANA ";

		return $sql;
	}

	/**
	 *  市区町村町CDからデータ取得用SQL
	 */
	public function generateCitySql($params, &$db_params)
	{

		// WHERE句生成
		$strWhere = "";
		for ($i = 0; $i < count($params['CityCd']); $i++)
		{
			// OR句の取得
			$strOr = ($i != 0) ? " OR" : "";
			// パラメータ
			$db_params['strCityCd_'.$i] = $params['CityCd'][$i];

			$strWhere .= $strOr." ( M01.M01_TODOUFUKEN_CD = substr( :strCityCd_".$i." ,1,2)";
			$strWhere .= " AND M01.M01_SHIKUCHOUSON_CD    = substr( :strCityCd_".$i." ,3,3))";
		}

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M01.M01_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD as SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS as TODOUFUKEN_ALIAS ";
		$sql .= "    ,to_single_byte(M01.M01_SHIKUCHOUSON) as SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_ALIAS as SHIKUCHOUSON_ALIAS ";
		$sql .= "    ,M44.M44_SITEITOSI_CD as SITEITOSI_CD ";
		$sql .= "    ,to_single_byte(M44.M44_SITEITOSI) as SITEITOSI ";
		$sql .= "    ,M44.M44_SITEITOSI_ROMAN as SITEITOSI_ROMAN ";
		$sql .= "    ,M44.M44_SITEITOSI_ALIAS as SITEITOSI_ALIAS ";
		$sql .= "FROM ";
		$sql .= "     M01JYUUSHO M01 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "    ,M44SITEITOSI M44 ";
		$sql .= "    ,M44SITEITOSI_SIKU M44_SIKU ";
		$sql .= "WHERE ";
		$sql .= "        trim(M01_ABOLISH_YM) is null ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = M44_SIKU.M44_TODOUFUKEN_CD(+) ";
		$sql .= "    AND M01.M01_SHIKUCHOUSON_CD = M44_SIKU.M44_SHIKUCHOUSON_CD(+) ";
		$sql .= "    AND M44_SIKU.M44_SITEITOSI_CD = M44.M44_SITEITOSI_CD(+) ";
		$sql .= "    AND (" . $strWhere . ") ";
		$sql .= "GROUP BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_ALIAS ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";
		$sql .= "    ,M44.M44_SITEITOSI_CD ";
		$sql .= "    ,M44.M44_SITEITOSI ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "    ,M44.M44_SITEITOSI_ROMAN ";
		$sql .= "    ,M44.M44_SITEITOSI_ALIAS ";
		$sql .= "ORDER BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";

		return $sql;
	}

	/**
	 *  市区町村町CDからデータ取得用（地図検索）SQL
	 *  地図検索の場合、政令指定都市は検索しない
	 */
	public function generateChizuCitySql($params, &$db_params)
	{

		// パラメータ
		$db_params['strCityCd_0'] = $params['CityCd'];

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M01.M01_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD as SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS as TODOUFUKEN_ALIAS ";
		$sql .= "    ,to_single_byte(M01.M01_SHIKUCHOUSON) as SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_ALIAS as SHIKUCHOUSON_ALIAS ";
		$sql .= "    ,'' as SITEITOSI_CD ";
		$sql .= "    ,'' as SITEITOSI ";
		$sql .= "FROM ";
		$sql .= "     M01JYUUSHO M01 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		//$sql .= "    ,M44SITEITOSI M44 ";
		//$sql .= "    ,M44SITEITOSI_SIKU M44_SIKU ";
		$sql .= "WHERE ";
		$sql .= "        trim(M01_ABOLISH_YM) is null ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		//$sql .= "    AND M01.M01_TODOUFUKEN_CD = M44_SIKU.M44_TODOUFUKEN_CD(+) ";
		//$sql .= "    AND M01.M01_SHIKUCHOUSON_CD = M44_SIKU.M44_SHIKUCHOUSON_CD(+) ";
		//$sql .= "    AND M44_SIKU.M44_SITEITOSI_CD = M44.M44_SITEITOSI_CD(+) ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = substr( :strCityCd_0 ,1,2) ";
		$sql .= "    AND M01.M01_SHIKUCHOUSON_CD = substr( :strCityCd_0 ,3,3) ";
		$sql .= "GROUP BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_ALIAS ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";
		//$sql .= "    ,M44.M44_SITEITOSI_CD ";
		//$sql .= "    ,M44.M44_SITEITOSI ";
		//$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "ORDER BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		//$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";

		return $sql;
	}

	/**
	 *  指定都市CDからデータ取得用SQL
	 */
	public function generateSiteiSql($params, &$db_params)
	{

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M44.M44_SITEITOSI_CD as SITEITOSI_CD ";
		$sql .= "    ,to_single_byte(M44.M44_SITEITOSI) as SITEITOSI ";
		$sql .= "    ,M44.M44_SITEITOSI_ROMAN as SITEITOSI_ROMAN ";
		$sql .= "    ,M44.M44_SITEITOSI_ALIAS as SITEITOSI_ALIAS ";
		$sql .= "    ,M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS as TODOUFUKEN_ALIAS ";
		$sql .= "FROM ";
		$sql .= "     M44SITEITOSI M44 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "WHERE ";
		$sql .= "        M44.M44_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		$sql .= "    AND " . $this->createWhere('M44.M44_SITEITOSI_CD', $params['SiteitosiCd']) . " ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";

		return $sql;
	}

	/**
	 *  都道府県CDからデータ取得用SQL
	 */
	public function generatePrefSql($params, &$db_params)
	{

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS as TODOUFUKEN_ALIAS ";
		$sql .= "    ,M01.M01_AREA_CD as AREA_CD ";
		$sql .= "    ,M01.M01_AREA_NAME as AREA ";
		$sql .= "    ,M01.M01_AREA_ROMAN_NAME as AREA_ROMAN ";
		$sql .= "FROM ";
		$sql .= "     M43TODOUFUKEN M43 ";
		$sql .= "    ,M01AREA M01 ";
		$sql .= "WHERE ";
		$sql .= "        " . $this->createWhere('M43.M43_TODOUFUKEN_CD', $params['PrefCd']) . " ";
		$sql .= "    AND M43.M43_TODOUFUKEN_CD = M01.M01_TODOUFUKEN_CD ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";

		return $sql;
	}


    /**
     * 学校名データ検索用SQL文の取得
     *
     * 学校名一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 町名データ検索用SQL
     */
    public function generateGakkoSql($params,&$db_params)
    {
		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    b.T67_SCH_CD, ";
		$sql .= "    a.V03_SCH_NAME, ";
		$sql .= "    a.V03_JANRU_CD, ";
		$sql .= "    a.V03_TODOUFUKEN_CD, ";
		$sql .= "    a.V03_TODOUFUKEN || a.V03_SHIKUCHOUSON AS SHOZAICHI ";
		$sql .= "FROM ";
		$sql .= "    SCHOOL_USER.V03SCHOOL a, ";
		$sql .= "    SCHOOL_USER.T67SCHOOL_TOUROKU b, ";
		$sql .= "    M05TENPO_INFO ";
		$sql .= "WHERE ";
		$sql .= "    a.V03_SCH_CD=b.T67_SCH_CD AND ";
		$sql .= "    b.T67_STATUS='3' AND ";
		$sql .= "    M05_TENPO_CD = B.T67_TENPO_CD(+) ";
		$sql .= " AND ";
		$sql .= "    (UPPER(a.V03_SCH_NAME) LIKE '%".$params['sch_name']."%'  ";
		$sql .= "      OR  ";
		$sql .= "    UPPER(a.V03_SCH_NAME) LIKE '%".$params['sch_name']."%') AND ";
		$sql .= "    a.V03_JANRU_CD = '".$params['janru_cd']."'  ";

		$sql .= "GROUP BY ";
		$sql .= "    b.T67_SCH_CD, ";
		$sql .= "    a.V03_SCH_NAME, ";
		$sql .= "    a.V03_JANRU_CD, ";
		$sql .= "    a.V03_TODOUFUKEN_CD, ";
		$sql .= "    a.V03_TODOUFUKEN, ";
		$sql .= "    a.V03_SHIKUCHOUSON ";
		$sql .= "ORDER BY ";
		$sql .= "    a.V03_JANRU_CD DESC, ";
		$sql .= "    a.V03_TODOUFUKEN_CD, ";
		$sql .= "    a.V03_SCH_NAME ";
        return $sql;
    }

	/**
	 *  住所データコンバート
	 */
	public function convertJyusyoData($result, &$data)
	{
		// 初期化
		if (!isset($data['AreaCd']))      $data['AreaCd']      = array();
		if (!isset($data['PrefCd']))      $data['PrefCd']      = array();
		if (!isset($data['SiteitosiCd'])) $data['SiteitosiCd'] = array();
		if (!isset($data['CityCd']))      $data['CityCd']      = array();
		if (!isset($data['TownCd']))      $data['TownCd']      = array();

		// 結果ループ
		foreach ($result as $jyusyo)
		{
			// エリア
			$areaCd = $jyusyo['AREA_CD'];
			if (!empty($areaCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($areaCd, $data['AreaCd']) == false)
				{
					$data['AreaCd'][$areaCd]['AREA_NAME']  = $jyusyo['AREA'];
					$data['AreaCd'][$areaCd]['AREA_ROMAN'] = $jyusyo['AREA_ROMAN'];
				}
			}
			// 都道府県
			$prefCd = $jyusyo['TODOUFUKEN_CD'];
			if (!empty($prefCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($prefCd, $data['PrefCd']) == false)
				{
					$data['PrefCd'][$prefCd]['PREF_NAME']  = $jyusyo['TODOUFUKEN'];
					$data['PrefCd'][$prefCd]['PREF_ALIAS'] = $jyusyo['TODOUFUKEN_ALIAS'];
				}
			}
			// 政令指定都市
			$siteiCd = $jyusyo['SITEITOSI_CD'];
			if (!empty($siteiCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($siteiCd, $data['SiteitosiCd']) == false)
				{
					$data['SiteitosiCd'][$siteiCd]['SITEI_NAME']  = $jyusyo['SITEITOSI'];
					$data['SiteitosiCd'][$siteiCd]['SITEI_ROMAN'] = $jyusyo['SITEITOSI_ROMAN'];
					$data['SiteitosiCd'][$siteiCd]['SITEI_ALIAS'] = $jyusyo['SITEITOSI_ALIAS'];
					$data['SiteitosiCd'][$siteiCd]['SITEI_FILL']  = $jyusyo['TODOUFUKEN'].$jyusyo['SITEITOSI'];
				}
			}
			// 市区町村
			$sikuCd = $jyusyo['SHIKUCHOUSON_CD'];
			if (!empty($sikuCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($sikuCd, $data['CityCd']) == false)
				{
					$data['CityCd'][$sikuCd]['CITY_NAME']  = $jyusyo['SHIKUCHOUSON'];
					$data['CityCd'][$sikuCd]['CITY_ALIAS'] = $jyusyo['SHIKUCHOUSON_ALIAS'];
					$data['CityCd'][$sikuCd]['CITY_FULL']  = $jyusyo['TODOUFUKEN'].$jyusyo['SHIKUCHOUSON'];
				}
			}
			// 町名
			$cyoCd = $jyusyo['CHOUMEI_CD'];
			if (!empty($cyoCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($cyoCd, $data['TownCd']) == false)
				{
					$data['TownCd'][$cyoCd]['TOWN_NAME']  = $jyusyo['CHOUMEI'];
					$data['TownCd'][$cyoCd]['TOWN_ALIAS'] = $jyusyo['CHOUMEI_ALIAS'];
					$data['TownCd'][$cyoCd]['TOWN_FULL']  = $jyusyo['SHIKUCHOUSON'].$jyusyo['CHOUMEI'];
				}
			}
		}
	}

	/**
	 *  駅CDからデータ取得用SQL
	 */
	public function generateStSql($params, &$db_params)
	{

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS as TODOUFUKEN_ALIAS ";
		$sql .= "    ,M02.M02_ENSEN_CD as ENSEN_CD ";
		$sql .= "    ,to_single_byte(M02.M02_ENSEN_NAME) as ENSEN_NAME ";
		$sql .= "    ,M02.M02_ENSEN_ALIAS as ENSEN_ALIAS ";
		$sql .= "    ,M03.M03_EKI_CD as EKI_CD ";
		$sql .= "    ,CASE";
		$sql .= "        WHEN M03.M03_EKI_NAME = '＊＊＊' THEN M43.M43_TODOUFUKEN || to_single_byte(M03.M03_ENSEN_NAME) ";
		$sql .= "     ELSE to_single_byte(M03.M03_EKI_NAME) ";
		$sql .= "     END AS EKI_NAME ";
		$sql .= "    ,M03.M03_EKI_ALIAS as EKI_ALIAS ";
		$sql .= "FROM ";
		$sql .= "     M03EKI M03 ";
		$sql .= "    ,M02ENSEN M02 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "WHERE ";
		$sql .= "        M03.M03_ENSEN_CD = M02.M02_ENSEN_CD ";
		$sql .= "    AND M03.M03_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		if (is_array($params['StCd'])) {
			$sql .= "    AND " . $this->createWhere('M03.M03_EKI_CD', $params['StCd']) . " ";
		} else {
			$sql .= "    AND M03.M03_EKI_CD = '" . $params['StCd'] . "' ";
		}
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ,M02.M02_ENSEN_KUBUN ";
		$sql .= "    ,M02.M02_ENSEN_KANA ";
		$sql .= "    ,M03.M03_EKI_CD ";

		return $sql;
	}

	/**
	 *  沿線CDからデータ取得用SQL
	 */
	public function generateRailSql($params, &$db_params)
	{

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS as TODOUFUKEN_ALIAS ";
		$sql .= "    ,M02.M02_ENSEN_CD as ENSEN_CD ";
		$sql .= "    ,to_single_byte(M02.M02_ENSEN_NAME) as ENSEN_NAME ";
		$sql .= "    ,M02.M02_ENSEN_ALIAS as ENSEN_ALIAS ";
		$sql .= "FROM ";
		$sql .= "     M04ENSEN_AREA M04 ";
		$sql .= "    ,M02ENSEN M02 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "WHERE ";
		$sql .= "        M04.M04_ENSEN_CD = M02.M02_ENSEN_CD ";
		$sql .= "    AND M04.M04_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		$sql .= "    AND " . $this->createWhere('M04.M04_TODOUFUKEN_CD', $params['PrefCd']) . " ";
		$sql .= "    AND " . $this->createWhere('M04.M04_ENSEN_CD', $params['RailCd']) . " ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ,M02.M02_ENSEN_KUBUN ";
		$sql .= "    ,M02.M02_ENSEN_KANA ";

		return $sql;
	}

	/**
	 *  沿線・駅データコンバート
	 */
	public function convertEnsenekiData($result, &$data)
	{
		// 初期化
		if (!isset($data['AreaCd'])) $data['AreaCd'] = array();
		if (!isset($data['PrefCd'])) $data['PrefCd'] = array();
		if (!isset($data['RailCd'])) $data['RailCd'] = array();
		if (!isset($data['StCd']))   $data['StCd']   = array();

		// 結果ループ
		foreach ($result as $rail)
		{
			// エリア
			$areaCd = $rail['AREA_CD'];
			if (!empty($areaCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($areaCd, $data['AreaCd']) == false)
				{
					$data['AreaCd'][$areaCd]['AREA_NAME']  = $rail['AREA'];
					$data['AreaCd'][$areaCd]['AREA_ROMAN'] = $rail['AREA_ROMAN'];
				}
			}
			// 都道府県
			$prefCd = $rail['TODOUFUKEN_CD'];
			if (!empty($prefCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($prefCd, $data['PrefCd']) == false)
				{
					$data['PrefCd'][$prefCd]['PREF_NAME']  = $rail['TODOUFUKEN'];
					$data['PrefCd'][$prefCd]['PREF_ALIAS'] = $rail['TODOUFUKEN_ALIAS'];
				}
			}
			// 沿線
			$ensenCd = $rail['ENSEN_CD'];
			if (!empty($ensenCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($ensenCd, $data['RailCd']) == false)
				{
					$data['RailCd'][$ensenCd]['RAIL_NAME']  = $rail['ENSEN_NAME'];
					$data['RailCd'][$ensenCd]['RAIL_ALIAS'] = $rail['ENSEN_ALIAS'];
				}
			}
			// 駅
			$ekiCd = $rail['EKI_CD'];
			if (!empty($ekiCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($ekiCd, $data['StCd']) == false)
				{
					$data['StCd'][$ekiCd]['ST_NAME']  = $rail['EKI_NAME'];
					$data['StCd'][$ekiCd]['ST_ALIAS'] = $rail['EKI_ALIAS'];
					$data['StCd'][$ekiCd]['ST_NAME2'] = $rail['EKI_NAME2'];
					$data['StCd'][$ekiCd]['ST_FULL']  = $rail['ENSEN_NAME'].$rail['EKI_NAME'];
					$data['StCd'][$ekiCd]['ST_FULL2'] = $rail['ENSEN_NAME'].$rail['EKI_NAME2'];
				}
			}
		}
	}

	/**
	 *  路線図駅CDからデータ取得用SQL
	 */
	public function generateStcodeSql($params, &$db_params)
	{

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS as TODOUFUKEN_ALIAS ";
		$sql .= "    ,M03.M03_ROSENZU_EKI_CD as EKI_CD ";
		$sql .= "    ,CASE";
		$sql .= "        WHEN M03.M03_EKI_NAME = '＊＊＊' THEN M43.M43_TODOUFUKEN || to_single_byte(M03.M03_ENSEN_NAME) ";
		$sql .= "     ELSE to_single_byte(M03.M03_EKI_NAME) ";
		$sql .= "     END AS EKI_NAME ";
		$sql .= "    ,CASE";
		$sql .= "        WHEN M03.M03_EKI_NAME = '＊＊＊' THEN M43.M43_TODOUFUKEN || to_single_byte(M03.M03_ENSEN_NAME) ";
		$sql .= "     ELSE to_single_byte(M03.M03_EKI_NAME) || '駅' ";
		$sql .= "     END AS EKI_NAME2 ";
		$sql .= "    ,M03.M03_EKI_ALIAS as EKI_ALIAS ";
		$sql .= "FROM ";
		$sql .= "     M03EKI M03 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "WHERE ";
		$sql .= "        M03.M03_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		$sql .= "    AND " . $this->createWhere('M03.M03_ROSENZU_EKI_CD', $params['stcode']) . " ";
		$sql .= "GROUP BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN ";
		$sql .= "    ,M43.M43_TODOUFUKEN_ALIAS ";
		$sql .= "    ,M03.M03_ROSENZU_EKI_CD ";
		$sql .= "    ,M03.M03_ENSEN_NAME ";
		$sql .= "    ,M03.M03_EKI_NAME ";
		$sql .= "    ,M03.M03_EKI_ALIAS ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ,M03.M03_ROSENZU_EKI_CD ";

		return $sql;
	}

	/**
	 *  路線図データコンバート
	 */
	public function convertRosenzuData($result, &$data)
	{
		// 初期化
		if (!isset($data['PrefCd'])) $data['PrefCd']  = array();
		if (!isset($data['stcode'])) $data['stcode']  = array();

		// 結果ループ
		foreach ($result as $rosenzu)
		{
			// 都道府県
			$prefCd = $rosenzu['TODOUFUKEN_CD'];
			if (!empty($prefCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($prefCd, $data['PrefCd']) == false)
				{
					$data['PrefCd'][$prefCd]['PREF_NAME']  = $rosenzu['TODOUFUKEN'];
					$data['PrefCd'][$prefCd]['PREF_ALIAS'] = $rosenzu['TODOUFUKEN_ALIAS'];
				}
			}
			// 駅
			$ekiCd = $rosenzu['EKI_CD'];
			if (!empty($ekiCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($ekiCd, $data['stcode']) == false)
				{
					$data['stcode'][$ekiCd]['STCODE_NAME']  = $rosenzu['EKI_NAME'];
					$data['stcode'][$ekiCd]['STCODE_ALIAS'] = $rosenzu['EKI_ALIAS'];
					$data['stcode'][$ekiCd]['STCODE_NAME2'] = $rosenzu['EKI_NAME2'];
				}
			}
		}
	}

	/**
	 *  学校CD（一覧）からデータ取得用SQL
	 */
	public function generateSchCdListSql($params, &$db_params)
	{

		$db_params['sch_cd'] = $params['sch_cd'];
		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "    b.T67_SCH_CD as SCH_CD, ";
		$sql .= "    a.V03_SCH_NAME as SCH_NAME ";
		$sql .= "FROM ";
		$sql .= "    SCHOOL_USER.V03SCHOOL a, ";
		$sql .= "    SCHOOL_USER.T67SCHOOL_TOUROKU b ";
		$sql .= "WHERE ";
		$sql .= "    a.V03_SCH_CD = b.T67_SCH_CD AND ";
		$sql .= "    b.T67_STATUS = '3' AND ";
		$sql .= "    b.T67_SCH_CD = :sch_cd ";

		return $sql;
	}

	/**
	 *  学校CD（地図）からデータ取得用SQL
	 */
	public function generateSchCdMapSql($params, &$db_params)
	{

		$db_params['sch_cd'] = $params['sch_cd'];
		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "    M90_LANDMARK_CD as SCH_CD, ";
		$sql .= "    M90_LANDMARK_NAME as SCH_NAME ";
		$sql .= "FROM ";
		$sql .= "    M90LANDMARK ";
		$sql .= "WHERE ";
		$sql .= "    M90_LANDMARK_CD = :sch_cd ";

		return $sql;
	}


    /**
     * 町名取得用SQL文の取得
     *
     * 町名取得SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 町名取得SQL
     */
    public function generateCyoNameByCyoCdSql($params, &$db_params)
    {

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    distinct M01_TODOUFUKEN_CD || M01_SHIKUCHOUSON_CD || M01_CHOUMEI_CD as CHOUMEI_CD ";
		$sql .= "    ,to_single_byte(M01_CHOUMEI) as CHOUMEI ";
		$sql .= "    ,decode(nvl(M44_SITEITOSI_CD,0),20,to_single_byte(M01_CHOUMEI),M01_SHIKUCHOUSON || to_single_byte(M01_CHOUMEI)) as CHOUMEI_ALIAS ";
		$sql .= "    ,M01_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,to_single_byte(M01_SHIKUCHOUSON) as SHIKUCHOUSON ";
		$sql .= "FROM ";
		$sql .= "    M01JYUUSHO ";
		$sql .= "   ,M44SITEITOSI_SIKU ";
		$sql .= "WHERE ";
		$sql .=      $this->createWhereFromPrefCdToTownCd("M01_TODOUFUKEN_CD","M01_SHIKUCHOUSON_CD","M01_CHOUMEI_CD",$params['TownCd'],"");
		$sql .= "  AND M01_TODOUFUKEN_CD = M44_TODOUFUKEN_CD(+) ";
		$sql .= "  AND M01_SHIKUCHOUSON_CD = M44_SHIKUCHOUSON_CD(+) ";
        $sql .= "ORDER BY ";
        $sql .= "    CHOUMEI_CD ";

        return $sql;
    }

   /**
	* 都道府県・市区町村・町コードWhere句作成
	*
	* @access	public
	* @param	string
	* @param	string
	* @param	array
	* @param	string
	* @return	void
	*/
	public function createWhereFromPrefCdToTownCd($colums1,$colums2,$colums3,$params,$option)
	{
		// コード数分ループ
		for($i=0;$i<count($params);$i++){

			if($i == 0){
				$sql .= " ( (    ".$colums1." = '".substr($params[$i],0,2)."'";
				$sql .= "    AND ".$colums2." = '".substr($params[$i],2,3)."' ";
				$sql .= "    AND ".$colums3." = '".substr($params[$i],5,3)."' ".$option.")";
			}else{
				$sql .= " OR (    ".$colums1." = '".substr($params[$i],0,2)."'";
				$sql .= "     AND ".$colums2." = '".substr($params[$i],2,3)."' ";
				$sql .= "     AND ".$colums3." = '".substr($params[$i],5,3)."' ".$option.")";
			}
		}

		$sql .= ")";

		return $sql;
    }

    /**
     * 市区町村名取得用SQL文の取得
     *
     * 市区町村名取得SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 市区町村名取得SQL
     */
    public function generateShikucyosonNameByShikucyosonCdSql($params, &$db_params)
    {
		$sql = "";
		$sql .= " SELECT ";
		$sql .= "    distinct M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD as SHIKUCHOUSON_CD ";
		$sql .= "    ,to_single_byte(M01.M01_SHIKUCHOUSON) as SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_ALIAS as SHIKUCHOUSON_ALIAS ";
		$sql .= "    ,M44.M44_SITEITOSI_CD as SITEITOSI_CD ";
		$sql .= "    ,to_single_byte(M44.M44_SITEITOSI) as SITEITOSI ";
		$sql .= "    ,M44.M44_SITEITOSI_ROMAN as SITEITOSI_ROMAN ";
		$sql .= "    ,M44.M44_SITEITOSI_ALIAS as SITEITOSI_ALIAS ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA as SITEITOSI_KN ";
		$sql .= "    ,M01.M01_TODOUFUKEN as TODOUFUKEN ";
		$sql .= " FROM ";
		$sql .= "     M01JYUUSHO M01 ";
		$sql .= "    ,M44SITEITOSI M44 ";
		$sql .= "    ,M44SITEITOSI_SIKU M44_SIKU ";
		$sql .= " WHERE ";
		$sql .= "        trim(M01.M01_ABOLISH_YM) is null ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = M44_SIKU.M44_TODOUFUKEN_CD(+) ";
		$sql .= "    AND M01.M01_SHIKUCHOUSON_CD = M44_SIKU.M44_SHIKUCHOUSON_CD(+) ";
		$sql .= "    AND M44_SIKU.M44_SITEITOSI_CD = M44.M44_SITEITOSI_CD(+) ";
		$sql .= "    AND " . $this->createWhereTown("M01.M01_TODOUFUKEN_CD","M01.M01_SHIKUCHOUSON_CD",$params['CityCd'],"");
		$sql .= " ORDER BY ";
		$sql .= "     M44.M44_SITEITOSI_KANA ";
		$sql .= "    ,SHIKUCHOUSON_CD ";

        return $sql;
    }

   /**
	* 都道府県・市区町村コードWhere句作成
	*
	* @access	public
	* @param	string
	* @param	string
	* @param	array
	* @param	string
	* @return	void
	*/
	public function createWhereTown($colums1,$colums2,$params,$option)
	{
		// コード数分ループ
		for($i=0;$i<count($params);$i++){

			if($i == 0){
				$sql = " (( ".$colums1." = '".substr($params[$i],0,2)."' AND ".$colums2." = '".substr($params[$i],2,3)."' ".$option.")";
			}else{
				$sql.= " OR (".$colums1." = '".substr($params[$i],0,2)."' AND ".$colums2." = '".substr($params[$i],2,3)."' ".$option.")";
			}
		}

		$sql .= ")";

		return $sql;
    }

   /**
     * 駅名取得用SQL文の取得
     *
     * 駅名用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	array 駅名取得用SQL
     */
    public function generateStNameByStCdSql($params, &$db_params)
    {

    	foreach($params['StCd'] as $key => $val){
    		$db_params['stCd' . $key] = $val;
    	}

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    distinct M03_EKI_CD as EKI_CD ";
		$sql .= "    ,CASE";
		$sql .= "        WHEN M03_EKI_NAME = '＊＊＊' THEN M43_TODOUFUKEN || to_single_byte(M03_ENSEN_NAME) ";
		$sql .= "     ELSE to_single_byte(M03_EKI_NAME) ";
		$sql .= "     END AS EKI_NAME ";
		$sql .= "    ,CASE";
		$sql .= "        WHEN M03_EKI_NAME = '＊＊＊' THEN M43_TODOUFUKEN || to_single_byte(M03_ENSEN_NAME) ";
		$sql .= "     ELSE to_single_byte(M03_EKI_NAME) || '駅' ";
		$sql .= "     END AS EKI_NAME2 ";
		$sql .= "    ,M03_EKI_ALIAS as EKI_ALIAS ";
		$sql .= "    ,to_single_byte(M03_ENSEN_NAME) as ENSEN_NAME ";
		$sql .= "FROM ";
		$sql .= "    M03EKI ";
		$sql .= "   ,M43TODOUFUKEN ";
		$sql .= "WHERE ";
		$sql .= "        M03_EKI_CD IN ( ";
		$sepa = "";
		foreach($params['StCd'] as $key => $val){
			$sql .= $sepa . " :stCd" . $key;
			$sepa = ",";
		}
		$sql .= "        ) ";
		$sql .= "  AND M03_TODOUFUKEN_CD = M43_TODOUFUKEN_CD ";
		$sql .= "ORDER BY ";
		$sql .= "        M03_EKI_CD ";

        return $sql;
    }

   /**
     * 沿線名取得用SQL文の取得
     *
     * 沿線名用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	array 沿線名取得用SQL
     */
    public function generateRailNameByRailCdSql($params, &$db_params)
    {

    	foreach($params['RailCd'] as $key => $val){
    		$db_params['railCd' . $key] = $val;
    	}

    	$sql = "";
		$sql .= "SELECT ";
		$sql .= "    distinct M03_ENSEN_CD as ENSEN_CD ";
		$sql .= "    ,to_single_byte(M03_ENSEN_NAME) as ENSEN_NAME ";
		$sql .= "    ,M02_ENSEN_ALIAS as ENSEN_ALIAS ";
		$sql .= "FROM ";
		$sql .= "    M03EKI ";
		$sql .= "   ,M02ENSEN ";
		$sql .= "WHERE ";
		$sql .= "        M03_ENSEN_CD IN ( ";
		$sepa = "";
		foreach($params['RailCd'] as $key => $val){
			$sql .= $sepa . " :railCd" . $key;
			$sepa = ",";
		}
		$sql .= "        ) ";
		$sql .= "  AND M03_ENSEN_CD = M02_ENSEN_CD ";
		$sql .= "ORDER BY ";
		$sql .= "        M03_ENSEN_CD ";

        return $sql;
    }

   /**
     * HP作成契約検索用法人CD取得SQL
     *
     * @access	public
     * @param	array
     * @param	array
     * @return	array 沿線名取得用SQL
     */
    public function generateHpkeiyakuHouzinCdSql($params, &$db_params)
    {

    	$db_params['keiyaku_cd'] = $params['keiyaku_cd'];

    	$sql = "";
		$sql .=" SELECT";
		$sql .="         T73_HOUZIN_CD";
		$sql .="         ,T73_KEIYAKU_CD";
		$sql .="     FROM";
		$sql .="         T73TENPO_HP_KEIYAKU";
		$sql .="     WHERE";
		$sql .="         T73_KEIYAKU_CD = : keiyaku_cd ";
		$sql .="         AND T73_OSUSUME_USE_FLAG = 1";

        return $sql;
    }

}
?>
